/**
 * 
 */
package goodroommate.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import goodroommate.dal.DatabaseSession;
import goodroommate.dao.ICommentDao;
import goodroommate.domain.Comment;
import goodroommate.exception.DatabaseException;

/**
 * @author ZhouWuAi
 *
 */
public class CommentDao implements ICommentDao {

	/* (non-Javadoc)
	 * @see goodroommate.dao.ICommentDao#addComment(goodroommate.dal.DatabaseSession, goodroommate.domain.Comment)
	 */
	@Override
	public void addComment(DatabaseSession dbs, Comment comment) throws DatabaseException {

		String sqlFields;
		String sqlParams;
		List<Object> params = new LinkedList<>();
		
		sqlFields = "(Commentator,";
		sqlParams = "(?,";
		params.add(comment.getCommentator());
		
		if (comment.getReply() != 0) {
			sqlFields += "Reply,";
			sqlParams += "?,";
			params.add(comment.getReply());
		}
		
		sqlFields += "PostingID,Content)";
		sqlParams += "?,?)";
		params.add(comment.getPostingid());
		params.add(comment.getContent());
		
		String sql = "INSERT INTO comments " + sqlFields + " VALUES " + sqlParams;
		
		try {
			dbs.executeUpdate(sql, params);
		} catch (DatabaseException e) {
			e.printStackTrace();
			throw new DatabaseException(e);
		}
		
//		String sql=null;
//		try{
//			if(comment.getReply()!=0){
//				sql="INSERT INTO comments (Commentator,Reply,PostingID,Content,Time) VALUES (?,?,?,?,?)";
//				dbs.executeUpdate(sql, comment.getCommentator(),comment.getReply(),comment.getPostingid(),comment.getContent(),comment.getTime());		
//			}else{
//				sql="INSERT INTO comments (Commentator,PostingID,Content,Time) VALUES (?,?,?,?)";
//				dbs.executeUpdate(sql, comment.getCommentator(),comment.getPostingid(),comment.getContent(),comment.getTime());		
//			}
//			return;
//		}catch(DatabaseException e){
//			e.printStackTrace();
//			throw new DatabaseException(e);
//		}

	}

	@Override
	public LinkedList<Comment> getCommentByPostingID(DatabaseSession dbs, int postingid) throws DatabaseException {
		
//		String sql="SELECT comments.*,First.Username AS Commentatorusername,Second.Username AS Replyusername "
//				+ "FROM comments,users First,users Second "
//				+ "WHERE comments.Commentator=First.ID AND comments.Reply=Second.ID AND PostingID=?";
		String sql = "SELECT comments.*,First.Username AS Commentatorusername,Second.Username AS Replyusername "
				+ "FROM comments "
				+ "LEFT JOIN users First "
				+ "ON comments.Commentator=First.ID "
				+ "LEFT JOIN users Second "
				+ "ON comments.Reply=Second.ID "
				+ "WHERE PostingID=?";
		LinkedList<Comment> comments=new LinkedList<>();
		try{
			ResultSet rs=dbs.executeQuery(sql, postingid);
			while(rs.next()){
				Comment comment=new Comment();
				comment.setCommentid(rs.getInt("CommentID"));
				comment.setCommentator(rs.getInt("Commentator"));
				comment.setCommentatorusername(rs.getString("Commentatorusername"));
				comment.setReply(rs.getInt("Reply"));
				comment.setReplyusername(rs.getString("Replyusername"));
				comment.setPostingid(postingid);
				comment.setContent(rs.getString("Content"));
				comment.setTime(rs.getTimestamp("Time"));
				comments.add(comment);
			}
			return comments;
		}catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException(e);
		}
	}
	
	

}
